unit clsNatop;

interface

uses uDMCI, DbTables, ubiblioCI, sysutils;

type
  TNaturezaOper = class(TObject)
  private
    Codf_Codigo: string;
    Nato_Codigo: string;

  public
    Nato_Descricao: string;
    Nato_ContaDb: string;
    Nato_ContaCr: string;
    Nato_CodigoN: string;
    //auxiliar
    codigo_fiscal: string;
    function Gravar(p_Codf_Codigo: string): Byte;
    function Recuperar(p_ID_CODIGO, p_Nato_Codigo: string): Byte;
    function Excluir(p_Codf_Codigo: string): Byte;
    procedure Consultar(var p_q: TQuery; P_codf_codigo, p_Nato_Descricao: string);
    procedure Consultar2(var p_q: TQuery; p_ID_CODIGO, p_Nato_Descricao: string);
    function MaiorCodigo: string;
    function MaiorCodigo_Por_codfiscal(P_codf_codigo: string): string;
    function Retorna_Natureza(P_codf_codigo: string): string;
    procedure Consultar_Natureza(var p_q: TQuery);
    procedure Consultar_Natureza2(var p_q: TQuery; p_CodF_Descricao: string);
    procedure ConsultarCFOP(var p_q: TQuery; P_DESCRICAO: string);
  end;
implementation



{ TNaturezaOper }


procedure TNaturezaOper.Consultar;
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('Select * from NF_NATUREZAOPERACAO where DESCRICAO>=:k_DESCRICAO and ID_CODIGO=:K_ID_CODIGO order by DESCRICAO');
  p_q.parambyname('k_DESCRICAO').asstring := p_Nato_Descricao;
  p_q.parambyname('k_ID_CODIGO').AsString := trim(P_Codf_Codigo);
end;

procedure TNaturezaOper.Consultar2;
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('Select * from naturezaoperacao where ID_CODIGO=:a and DESCRICAO>=:b ' +
    'order by DESCRICAO');
  p_q.params[0].asstring := trim(p_ID_CODIGO);
  p_q.params[1].asstring := p_Nato_Descricao;
end;

function TNaturezaOper.Excluir(p_Codf_Codigo: string): Byte;
var
  tq: TQuery;
begin
  tq              := Tquery.Create(DMCI);
  tq.databasename := DMCI.dbCentralImpostos.DatabaseName;
  tq.active := False;
  tq.close;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('DELETE FROM NF_NATUREZAOPERACAO WHERE ID_CODIGO=:k_ID_CODIGO ');
  tq.parambyname('k_ID_CODIGO').AsString := trim(p_Codf_Codigo);
  try
    tq.execSQL;
    Result := 1;
  except
    result := 0;
  end;
  tq.close;
  tq.Free;

end;

function TNaturezaOper.Gravar(p_Codf_Codigo: string): Byte;
var
  tq: TQuery;
begin
  tq              := Tquery.Create(DMCI);
  tq.databasename := DMCI.dbCentralImpostos.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.params.clear;
  tq.sql.add('SELECT * FROM NF_NATUREZAOPERACAO where ID_CODIGO=:k_ID_CODIGO ');
  tq.parambyname('k_ID_CODIGO').asstring := trim(p_Codf_Codigo);
  tq.open;
  if tq.eof then begin
    tq.sql.clear;
    tq.params.clear;
    tq.sql.add('INSERT INTO NF_NATUREZAOPERACAO(ID_CODIGO,DESCRICAO) VALUES(:k_ID_CODIGO,' +
      ':K_DESCRICAO)');
  end
  else begin
    tq.sql.clear;
    tq.params.clear;
    tq.sql.add('UPDATE NF_NATUREZAOPERACAO SET ' +
      'DESCRICAO=:k_DESCRICAO ' +
      'WHERE ID_CODIGO=:k_ID_CODIGO');
  end;
  tq.parambyname('k_ID_CODIGO').asstring := trim(p_Codf_Codigo);
  tq.parambyname('k_DESCRICAO').asstring := copy(Nato_Descricao, 1, 500);
  tq.close;
  try
    tq.execsql;
    result := 1;
  except
    result := 0;
  end;
  tq.Free;
end;

function TNaturezaOper.MaiorCodigo: string;
var
  tq: TQuery;
begin
  tq              := Tquery.Create(DMCI);
  tq.databasename := DMCI.dbCentralImpostos.DatabaseName;
  tq.Active := false;
  tq.SQL.clear;
  tq.SQL.Add('select max(nato_codigo) as maior from NF_NATUREZAOPERACAO');
  tq.open;
  Result := tq.fieldbyname('maior').AsString;
  if result = '' then
    result := '0';
  tq.close;
  tq.Free;
end;

function TNaturezaOper.MaiorCodigo_Por_codfiscal(
  p_Codf_Codigo: string): string;
var
  tq: TQuery;
begin
  tq              := Tquery.Create(DMCI);
  tq.databasename := DMCI.dbCentralImpostos.DatabaseName;
  tq.Active := false;
  tq.SQL.clear;
  tq.SQL.Add('Select max(nato_codigo) as maior from NF_NATUREZAOPERACAO where ID_CODIGO=:K_ID_CODIGO');
  tq.ParamByName('K_ID_CODIGO').AsString := trim(P_codf_codigo);
  tq.open;
  Result := tq.fieldbyname('maior').AsString;
  if result = '' then result := '0';
  tq.close;
  tq.Free;
end;

function TNaturezaOper.Recuperar(p_ID_CODIGO, p_Nato_Codigo: string): Byte;
var
  tq: TQuery;
begin
  tq              := Tquery.Create(DMCI);
  tq.databasename := DMCI.dbCentralImpostos.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.sql.add('SELECT * FROM NF_NATUREZAOPERACAO where ID_CODIGO=:k_ID_CODIGO and nato_codigo=:K_nato_codigo');
  tq.Params[0].asstring := trim(p_ID_CODIGO);
  tq.Params[1].asstring := StrZero(p_nato_codigo, 2);
  tq.open;
  Codf_Codigo := trim(p_ID_CODIGO); //campo criado no formulario de consulta
  nato_codigo := P_nato_codigo; //campo criado no formulario de consulta
  if tq.eof then begin
    Codf_Codigo := '';
    Nato_Codigo := '';
    Nato_Descricao := '';
    Nato_ContaDb := '';
    Nato_ContaCr := '';
    Nato_CodigoN := '';
    Result := 0;
  end
  else begin
    codigo_fiscal := tq.fieldbyname('ID_CODIGO').asstring;
    Codf_Codigo := tq.fieldbyname('ID_CODIGO').asstring;
    nato_codigo := tq.fieldbyname('nato_codigo').AsString;
    Nato_Descricao := tq.fieldbyname('DESCRICAO').AsString;
    nato_contadb := tq.fieldbyname('nato_contadb').AsString;
    nato_contacr := tq.fieldbyname('nato_contacr').AsString;
    nato_codigon := tq.fieldbyname('Nato_codigon').asstring;
    Result := 1;
  end;
  tq.Close;
  tq.Free;
end;

function TNaturezaOper.Retorna_Natureza;
var
  tq: TQuery;
begin
  tq              := Tquery.Create(DMCI);
  tq.databasename := DMCI.dbCentralImpostos.DatabaseName;
  tq.Active := false;
  tq.SQL.clear;
  tq.SQL.Add('Select nato_codigo from NF_NATUREZAOPERACAO where ID_CODIGO=:a');
  tq.params[0].AsString := trim(P_codf_codigo);
  tq.open;
  Result := tq.fieldbyname('nato_codigo').AsString;
  tq.close;
  tq.Free;
end;

procedure TNaturezaOper.Consultar_Natureza(var p_q: TQuery);
var
  a : String;
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(PChar(' SELECT CODF_CODIGO, CODF_DESCRICAO AS NATO_DESCRICAO, CODF_CODIGO||'+#39+' - '+#39+'||CODF_DESCRICAO AS Descric FROM CODIGOFISCAL '));
  p_q.sql.add(' ORDER BY CODF_CODIGO ');
end;

procedure TNaturezaOper.Consultar_Natureza2(var p_q: TQuery; p_CodF_Descricao: string);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('SELECT CODF_CODIGO, CODF_DESCRICAO AS NATO_DESCRICAO FROM CODIGOFISCAL order by CODF_CODIGO ');

end;

procedure TNaturezaOper.ConsultarCFOP(var p_q: TQuery; P_DESCRICAO: string);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('SELECT ID_CODIGO, NATO_CODIGO, DESCRICAO, ID_CODIGO||DESCRICAO AS DESCRI FROM NF_NATUREZAOPERACAO WHERE DESCRICAO LIKE (:P_DESCRICAO) ORDER BY DESCRICAO');
  p_q.parambyname('P_DESCRICAO').asstring := P_DESCRICAO;
end;


end.

